﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data;
using System.Linq;
using DBUtil.Expressions;
using System.Threading;
using DBUtil.Util;
using System.ComponentModel;
using System.Threading.Tasks;
using DBUtil.UsingBlocks;
using DotNetCommon;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System.Data.Common;
using System.Text;

namespace DBUtil
{
    /// <summary>
    /// 通用数据库访问对象
    /// </summary>
    public abstract partial class DBAccess
    {
        #region 设置Settings
        /// <summary>
        /// 设置
        /// </summary>
        public DBSettings Settings { get; protected set; }
        #endregion

        #region 表名或标识符分析/解析
        /// <summary>
        /// 标识符引用标记<para></para>
        /// 如 sqlserver: [],""<para></para>
        /// 如 mysql: ``,""<para></para>
        /// </summary>
        public virtual List<string> QuoteIdentifierCharacters { get; }

        private char[][] _characters = null;
        protected virtual char[][] getQuoteIndentifierCharacters()
        {
            if (_characters == null)
            {
                _characters = new char[QuoteIdentifierCharacters.Count][];
                for (var i = 0; i < QuoteIdentifierCharacters.Count; i++)
                {
                    _characters[i] = QuoteIdentifierCharacters[i].ToArray();
                }
            }
            return _characters;
        }

        /// <summary>
        /// 从给定的表名或标识符中解析出纯净的 数据库、schema、标识符 名称<para></para>
        /// 如 sqlserver: [testdb].[dbo].[test] => (test,dbo,testdb)<para></para>
        /// 如 mysql: `testdb`.`test` => (testdb,testdb,test)<para></para>
        /// </summary>
        /// <param name="fullIdentifierName">给定的标识符全名</param>
        /// <returns></returns>
        public virtual (string dbName, string schemaName, string identityName) AnalysisIdentifier(string fullIdentifierName)
        {
            if (fullIdentifierName.IsNullOrEmptyOrWhiteSpace()) throw new ArgumentNullException(nameof(fullIdentifierName));
            fullIdentifierName = fullIdentifierName.Trim();
            var _characters = getQuoteIndentifierCharacters();
            var arr = fullIdentifierName.ToCharArray();
            var _quoteArr = _characters.Select(i => i[0]).ToList();
            var segArr = new List<string>();
            char protect = '\0';
            var t = "";
            for (var index = 0; index < arr.Length; index++)
            {
                var c = fullIdentifierName[index];
                if (protect == '\0')
                {
                    //尚未进入保护模式([],"",``)
                    if (_quoteArr.IndexOf(c) >= 0)
                    {
                        //进入保护模式
                        var _index = _quoteArr.IndexOf(c);
                        protect = _characters[_index][1];
                        segArr.Add(t);
                        t = "";
                        continue;
                    }
                    if (c == '.')
                    {
                        //进行拆分
                        segArr.Add(t);
                        t = "";
                        continue;
                    }
                    t += c;
                }
                else
                {
                    //已经在保护模式中
                    if (c == protect)
                    {
                        //结束保护模式
                        segArr.Add(t);
                        t = "";
                        protect = '\0';
                        continue;
                    }
                    else
                    {
                        t += c;
                        continue;
                    }

                }
            }
            if (t != "")
            {
                segArr.Add(t);
            }
            segArr = segArr.Where(i => !string.IsNullOrWhiteSpace(i)).ToList();
            if (segArr.Count == 1) return (null, null, segArr[0]);
            if (segArr.Count == 2) return (null, segArr[0], segArr[1]);
            if (segArr.Count == 3) return (segArr[0], segArr[1], segArr[2]);
            throw new Exception($"无法解析标识符:{fullIdentifierName}");
        }

        /// <summary>
        /// 从提供的参考表名和模式名中分离出纯净的表名和模式名<para></para>
        /// 参考: <seealso cref="DBAccess.AnalysisIdentifier(string)"/>
        /// </summary>
        /// <param name="identifierName">参考表示符名,如: testdb.table1 、 table1、[dbo].[table1]</param>
        /// <param name="schemaName">参考模式名,如果指定了这个值,那么将丢弃从 <c>identifierName</c> 参数中分析得来的schemaName </param>
        /// <remarks>注意：如果指定了schemaName则丢弃从 <c>identifierName</c> 参数中分析得来的schemaName</remarks>
        public virtual (string tableName, string schemaName) AnalysisIdentifierWithDefaultSchema(string identifierName, string schemaName = null)
        {
            //尝试从表名中分离schema名称
            var (_dbName, _schemaName, _identityName) = AnalysisIdentifier(identifierName);
            //优先使用明确指定了的schema名称
            if (!string.IsNullOrWhiteSpace(schemaName))
            {
                _schemaName = schemaName?.Trim();
            }
            //没有模式名称的话,取用户的默认schema名称
            if (string.IsNullOrWhiteSpace(_schemaName)) _schemaName = DefaultSchema;
            return (_identityName, _schemaName);
        }

        /// <summary>
        /// 返回指定标识符的引用写法<para></para>
        /// 如 sqlserver: table1 => [table1]<para></para>
        /// 如 mysql: "testdb".table1 => `testdb`.`table1`<para></para>
        /// </summary>
        /// <param name="identitiferName">如: table1、"testdb".table1、[dbo].[table1]、`testdb`.`table1`</param>
        /// <returns></returns>
        public virtual string QuoteIdentifier(string identitiferName)
        {
            var _characters = getQuoteIndentifierCharacters();
            (string dbName, string schemaName, string identityName) = AnalysisIdentifier(identitiferName);
            var sb = new StringBuilder();
            if (dbName.IsNotNullOrEmptyOrWhiteSpace()) sb.Append($"{_characters[0][0]}{dbName}{_characters[0][1]}.");
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace()) sb.Append($"{_characters[0][0]}{schemaName}{_characters[0][1]}.");
            sb.Append($"{_characters[0][0]}{identityName}{_characters[0][1]}");
            return sb.ToString();
        }

        /// <summary>
        /// 返回指定标识符的引用写法<para></para>
        /// 如 sqlserver: table1 => [table1]<para></para>
        /// 如 mysql: "testdb".table1 => `testdb`.`table1`<para></para>
        /// </summary>
        /// <param name="identitiferName">如: testdb.table1、table1、`table1`、`testdb`.`table1`</param>
        /// <param name="schemaName">参考模式名,如果指定了这个值,那么直接返回</param>
        /// <remarks>注意：如果指定了schemaName则丢弃从 <c>identifierName</c> 参数中分析得来的schemaName</remarks>
        /// <returns></returns>
        public virtual string QuoteIdentifierWithDefaultSchema(string identitiferName, string schemaName = null)
        {
            var _characters = getQuoteIndentifierCharacters();
            (string dbName, string _schemaName, string identityName) = AnalysisIdentifier(identitiferName);
            var sb = new StringBuilder();
            if (dbName.IsNotNullOrEmptyOrWhiteSpace()) sb.Append($"{_characters[0][0]}{dbName}{_characters[0][1]}.");
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace()) _schemaName = schemaName;
            if (_schemaName.IsNotNullOrEmptyOrWhiteSpace()) sb.Append($"{_characters[0][0]}{_schemaName}{_characters[0][1]}.");
            sb.Append($"{_characters[0][0]}{identityName}{_characters[0][1]}");
            return sb.ToString();
        }

        /// <summary>
        /// 返回指定标识符的引用写法(如果提供的identitifer本身没有使用引用写法)<para></para>
        /// 如 sqlserver: table1 => [table1]<para></para>
        /// 如 sqlserver: "table1" => "table1"<para></para>
        /// 如 mysql: `testdb`.`table1` => `testdb`.`table1`<para></para>
        /// 如 mysql: "table1" => "table1"<para></para>
        /// </summary>
        /// <param name="identitiferName">如: table1、"table1"、`testdb`.`table1`、[dbo].[table1]</param>
        /// <returns></returns>
        public virtual string QuoteIdentifierIfNot(string identitiferName)
        {
            identitiferName = identitiferName.Trim();
            var _characters = getQuoteIndentifierCharacters();
            var arr = identitiferName.ToCharArray();
            var _quoteArr = _characters.Select(i => i[0]).ToList();
            if (_quoteArr.Contains(arr[0])) return identitiferName;
            return QuoteIdentifier(identitiferName);
        }

        /// <summary>
        /// 去除标识符的引用标志,只保留最纯净名称<para></para>
        /// 如 sqlserver: [dbo].[table1] => table1<para></para>
        /// 如 sqlserver/mysql: testdb.table1 => table1<para></para>
        /// 如 mysql: `table1` => table1<para></para>
        /// </summary>
        /// <param name="identitiferName"></param>
        /// <returns></returns>
        public virtual string UnQuoteIdentifier(string identitiferName)
        {
            var _characters = getQuoteIndentifierCharacters();
            var name = AnalysisIdentifier(identitiferName).identityName;
            return name;
        }
        #endregion

        #region 创建DbCommand
        /// <summary>
        /// 创建DbCommand
        /// </summary>
        /// <returns></returns>
        protected abstract DbCommand CreateCommand();
        #endregion

        #region 创建DataAdapter
        /// <summary>
        /// 创建DataAdapter
        /// </summary>
        /// <returns></returns>
        protected abstract DataAdapter CreateAdapter(DbCommand cmd);

        /// <summary>
        /// 创建DataAdapter
        /// </summary>
        /// <returns></returns>
        protected abstract DataAdapter CreateAdapter();
        #endregion

        #region 长连接状态管理 OpenLongConnectOnce/CloseLongConnectOnce
        /// <summary>
        /// 是否保持连接不断开
        /// </summary>
        private bool IsKeepConnect => _keepConnectCounter > 0;

        /// <summary>
        /// 保持打开数据库连接的次数
        /// </summary>
        private int _keepConnectCounter = 0;

        /// <summary>
        /// 尝试打开一个长连接
        /// </summary>
        public IDisposable OpenLongConnectOnce()
        {
            _keepConnectCounter++;
            if (IsClose) Conn.Open();
            return new LongConnectBlock(this);
        }

        private ReaderLongConnectBlock _openReaderLongConnectOnce()
        {
            _keepConnectCounter++;
            if (IsClose) Conn.Open();
            return new ReaderLongConnectBlock(this);
        }

        /// <summary>
        /// 尝试停止一个长连接
        /// </summary>
        public void CloseLongConnectOnce()
        {
            _keepConnectCounter--;
            if (_keepConnectCounter < 0) _keepConnectCounter = 0;
            if (!IsTran && !IsKeepConnect)
            {
                Conn.Close();
            }
        }

        #endregion

        #region 基础属性        
        /// <summary>
        /// 事物对象
        /// </summary>
        public DbTransaction Tran { get; protected set; }
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string DBConn { get; protected set; }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public string DBType { get; protected set; }

        /// <summary>
        /// 数据库版本
        /// </summary>
        public virtual string DBVersion { get; }

        /// <summary>
        /// 当前连接的数据库名称
        /// </summary>
        public virtual string DataBase { get; }

        /// <summary>
        /// 默认的架构/模式
        /// </summary>
        public virtual string DefaultSchema { get; }

        /// <summary>
        /// 操作当前数据库的用户名(如果是在sqlserver下,这个值表示当前数据库内的用户名而不是登录数据库使用的用户名,例如: 这里显示的是dbo而不是sa)
        /// </summary>
        public virtual string UserName { get; }

        /// <summary>
        /// 登录数据库使用的用户名
        /// </summary>
        public virtual string LoginUserName { get; set; }

        /// <summary>
        /// 连接对象
        /// </summary>
        public DbConnection Conn { get; protected set; }
        /// <summary>
        /// 记录是否打开了连接,防止多次打开连接
        /// </summary>
        public bool IsOpen => Conn?.State == ConnectionState.Open;
        /// <summary>
        /// 记录是否关闭了连接,防止多次打开连接
        /// </summary>
        public bool IsClose => Conn?.State == ConnectionState.Closed;
        /// <summary>
        /// 记录是否开启了事务,防止多次开启事务
        /// </summary>
        public bool IsTran => Tran != null;

        /// <summary>
        /// 当前数据库使用的参数的前缀符号
        /// </summary>
        public string ParaPrefix { get; protected set; }
        #endregion

        #region 测试数据库连接
        /// <summary>
        /// 测试数据库连接
        /// </summary>
        public Result OpenTest()
        {
            try
            {
                using (OpenLongConnectOnce()) { }
                return Result.Ok();
            }
            catch (Exception ex)
            {
                return Result.NotOk(ex?.Message);
            }
        }
        #endregion

        #region 防止sql注入
        /// <summary>
        /// 处理用户输入字符串,防止sql注入
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public virtual string DealSqlInject(string str) => str?.Replace("'", "''");
        #endregion

        #region CreatePara 创建参数
        /// <summary>
        /// 创建参数
        /// </summary>
        public abstract IDataParameter CreatePara();

        /// <summary>
        /// 创建具有名称和值的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        public virtual IDataParameter CreatePara(string name, object value)
        {
            var para = CreatePara();
            para.ParameterName = name;
            para.Value = value;
            return para;
        }
        #endregion

        #region 执行sql语句 ExecuteSql...
        /// <summary>
        /// 执行单个sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回影响的行数</returns>
        public virtual int ExecuteSql(string strSql) => ExecuteSqlBatch(strSql);

        /// <summary>
        /// 执行多个sql语句
        /// </summary>
        /// <param name="sqlArray">多个SQL语句的数组</param>
        /// <returns>返回总共影响的行数</returns>
        public virtual int ExecuteSqlBatch(params string[] sqlArray)
        {
            var list = new List<ExecuteSqlBatchItem>();
            foreach (var sql in sqlArray)
            {
                list.Add(ExecuteSqlBatchItem.Create(sql));
            }
            return ExecuteSqlBatch(list.ToArray());
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int ExecuteSql(string strSql, params IDataParameter[] paras) => ExecuteSqlBatch(ExecuteSqlBatchItem.Create(strSql, paras));

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns>受影响的行数</returns>
        public virtual int ExecuteSql(string strSql, Dictionary<string, object> dic) => ExecuteSql(strSql, dic.ToParameters(this));

        /// <summary>
        /// 批量执行带参数的sql语句
        /// </summary>
        /// <param name="items">批量执行项，参考: <seealso cref="ExecuteSqlBatchItem.Create(string, IDataParameter[])"/> </param>
        /// <returns>总共影响的行数</returns>
        public virtual int ExecuteSqlBatch(params ExecuteSqlBatchItem[] items)
        {
            int counter = 0;
            var cmd = CreateCommand();
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            using (OpenLongConnectOnce())
            {
                foreach (var item in items)
                {
                    cmd.CommandText = item.Sql;
                    cmd.Parameters.Clear();
                    if (item.UseParameterArray && item.Parameters?.Length > 0)
                    {
                        foreach (var para in item.Parameters) cmd.Parameters.Add(para);
                    }
                    else if (!item.UseParameterArray && item.Dic?.Count > 0)
                    {
                        foreach (var para in item.Dic.ToParameters(this)) cmd.Parameters.Add(para);
                    }
                    counter += cmd.ExecuteNonQuery();
                }
                return counter;
            }
        }
        #endregion

        #region Insert...
        /// <summary>
        /// 向表中插入一行数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项是原生sql,请参照: <code>dic.Add(("GETDATE()",false))</code></param>
        /// <returns>受影响的行数</returns>
        public virtual int Insert(string tableName, Dictionary<string, object> dic) => InsertBatch(tableName, dic);

        /// <summary>
        /// 批量插入(相同表)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dics">多行数据</param>
        /// <returns>影响的行数</returns>
        public virtual int InsertBatch(string tableName, params Dictionary<string, object>[] dics) => InsertBatch(dics.Select(dic => InsertBatchItem.Create(tableName, dic)).ToArray());

        /// <summary>
        /// 批量插入数据(可批量向不同表插入)
        /// </summary>
        /// <param name="items">插入项，参照：<seealso cref="InsertBatchItem.Create(string, Dictionary{string, object})"/></param>
        /// <returns>影响的行数</returns>
        public virtual int InsertBatch(params InsertBatchItem[] items)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, item.TableName, item.Dic, prefix);
                sql += res.sql + "\r\n";
                paras.AddRange(res.paras);
            }
            return ExecuteSql(sql, paras.ToArray());
        }

        /// <summary>
        /// 插入一行数据并且返回自增的Id
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项是原生sql,请参照: <code>dic.Add(("GETDATE()",false))</code></param>
        /// <param name="sequenceName">序列名称</param>
        /// <returns></returns>
        public virtual long InsertAndGetId(string tableName, Dictionary<string, object> dic, string sequenceName = null) => InsertBatchAndGetIds(tableName, sequenceName, dic).FirstOrDefault();

        /// <summary>
        /// 插入多行数据(相同表)并且返回自增的Id集合
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="sequenceName">序列名称</param>
        /// <param name="dics">多行数据</param>
        /// <returns></returns>
        public virtual List<long> InsertBatchAndGetIds(string tableName, string sequenceName = null, params Dictionary<string, object>[] dics)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < dics.Length; i++)
            {
                var item = dics[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, tableName, item, prefix);
                sql += res.sql + "\r\n";
                sql += GetInsertedId(sequenceName);
                paras.AddRange(res.paras);
            }
            var dt = SelectDataTable(sql, paras.ToArray());
            return dt.Select().Select(i => i[0].To<long>()).ToList();
        }
        #endregion

        #region Update...
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setDic">以键值对表示的更新列</param>
        /// <param name="whereSql">过滤条件sql以and开头</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <returns>受影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> setDic, string whereSql, params IDataParameter[] parameters) =>
            UpdateBatch(UpdateBatchItem.Create(tableName, setDic, whereSql, parameters));

        /// <summary>
        /// 更新数据，使用字典作为过滤条件
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setDic">以键值对表示的更新列</param>
        /// <param name="whereDic">以键值对表示的过滤条件</param>
        /// <returns>受影响的行数</returns>
        public virtual int Update(string tableName, Dictionary<string, object> setDic, Dictionary<string, object> whereDic) =>
            UpdateBatch(UpdateBatchItem.Create(tableName, setDic, whereDic));

        /// <summary>
        /// 批量更新,不同表
        /// </summary>
        /// <param name="items">更新项数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateBatch(params UpdateBatchItem[] items)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                var (filterSql, paramter) = (item.WhereSql, item.Parameters);
                var hasPrefix = false;
                if (!item.UseRawSql)
                {
                    (filterSql, paramter) = SqlUtil.GenerateFilterSql(this, item.WhereDic, $"p_{i}", null);
                    hasPrefix = true;
                }
                var (sql2, paras2) = SqlUtil.GenerateUpdateSql(this, item.TableName, item.SetDic, hasPrefix ? "" : $"__p_{i}", filterSql, paramter);
                sql += "\r\n" + sql2;
                if (paras2 != null) paras.AddRange(paras2);
            }
            return ExecuteSql(sql, paras.ToArray());
        }
        #endregion

        #region UpdateOrInsert
        /// <summary>
        /// 插入或更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">以键值对表示的插入项</param>
        /// <param name="updateDic">以键值对表示的更新项</param>
        /// <param name="whereSql">用以决定是更新还是插入的where子句</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, string whereSql, params IDataParameter[] parameters)
        {
            using (OpenLongConnectOnce())
            {
                if (SelectScalar<string>($"select count(1) from {tableName} where 1=1 {whereSql}", parameters) == "0")
                {
                    return Insert(tableName, insertDic);
                }
                else
                {
                    return Update(tableName, updateDic, whereSql, parameters);
                }
            };
        }

        /// <summary>
        /// 插入或更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">以键值对表示的插入项</param>
        /// <param name="updateDic">以键值对表示的更新项</param>
        /// <param name="filterDic">用以生成where子句的字典，进一步决定是更新还是插入</param>
        /// <returns>受影响的行数</returns>
        public virtual int UpdateOrInsert(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, Dictionary<string, object> filterDic)
        {
            var (filterSql, paras2) = SqlUtil.GenerateFilterSql(this, filterDic, $"p_", null);
            return UpdateOrInsert(tableName, insertDic, updateDic, filterSql, paras2);
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSql">过滤条件以and开头</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <returns>受影响的行数</returns>
        public virtual int Delete(string tableName, string whereSql, params IDataParameter[] parameters)
            => DeleteBatch(DeleteBatchItem.Create(tableName, whereSql, parameters));

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">用于生成where子句的字典</param>
        /// <returns>受影响的行数</returns>
        public virtual int Delete(string tableName, Dictionary<string, object> whereDic)
            => DeleteBatch(tableName, whereDic);

        /// <summary>
        /// 批量删除(相同表)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereDics">用于生成where子句的字典数组</param>
        /// <returns>受影响的行数</returns>
        public virtual int DeleteBatch(string tableName, params Dictionary<string, object>[] whereDics)
            => DeleteBatch(whereDics.Select(i => DeleteBatchItem.Create(tableName, i)).ToArray());

        /// <summary>
        /// 批量删除(不同表)
        /// </summary>
        /// <param name="items">删除项</param>
        /// <returns></returns>
        public virtual int DeleteBatch(params DeleteBatchItem[] items)
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                var (filterSql, paramter) = (item.WhereSql, item.Parameters);
                if (!item.UseRawSql)
                {
                    (filterSql, paramter) = SqlUtil.GenerateFilterSql(this, item.WhereDic, $"p_{i}", null);
                }
                var resSql = SqlUtil.GenerateDeleteSql(this, item.TableName, filterSql);
                sql += resSql + "\r\n";
                if (paramter != null) paras.AddRange(paramter);
            }
            return ExecuteSql(sql, paras.ToArray());
        }
        #endregion

        #region 查询语句 Select...
        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql) => SelectScalar<T>(strSql, new IDataParameter[] { });

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数的字典</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql, Dictionary<string, object> dic) => SelectScalar<T>(strSql, dic.ToParameters(this));

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public virtual T SelectScalar<T>(string strSql, params IDataParameter[] paraArr)
        {
            object res = null;
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            using (OpenLongConnectOnce()) { res = cmd.ExecuteScalar(); }
            cmd.Parameters.Clear();
            return res.To<T>();
        }

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql) => SelectDataSet(strSql, new IDataParameter[] { });

        /// <summary>
        /// 返回查询结果的数据集,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql, Dictionary<string, object> dic) => SelectDataSet(strSql, dic.ToParameters(this));

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataSet SelectDataSet(string strSql, params IDataParameter[] paraArr)
        {
            DataSet set = new DataSet();
            var cmd = CreateCommand();
            cmd.Connection = Conn;
            cmd.CommandText = strSql;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            if (IsTran) cmd.Transaction = Tran;
            var adp = CreateAdapter(cmd);
            using (OpenLongConnectOnce()) { adp.Fill(set); };
            cmd.Parameters.Clear();
            return set;
        }

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql) => SelectDataTable(strSql, new IDataParameter[] { });

        /// <summary>
        /// 返回查询结果的数据表,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql, Dictionary<string, object> dic) => SelectDataTable(strSql, dic.ToParameters(this));

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回的查询结果集</returns>
        public virtual DataTable SelectDataTable(string strSql, params IDataParameter[] paraArr)
        {
            var ds = SelectDataSet(strSql, paraArr);
            if (ds.Tables.Count > 0) return ds.Tables[0];
            else return null;
        }

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql) => SelectDataReader(strSql, new IDataParameter[] { });

        /// <summary>
        /// 获取阅读器,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql, Dictionary<string, object> dic) => SelectDataReader(strSql, dic.ToParameters(this));

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <returns>返回阅读器</returns>
        public virtual ReaderLongConnectBlock SelectDataReader(string strSql, params IDataParameter[] paraArr)
        {
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            if (IsTran) cmd.Transaction = Tran;
            var block = _openReaderLongConnectOnce();
            IDataReader reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            block.Reader = reader;
            return block;
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql) where T : class, new() => SelectModels<T>(sql, new IDataParameter[] { }).FirstOrDefault();

        /// <summary>
        /// 查询到单个模型,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql, Dictionary<string, object> dic) where T : class, new() => SelectModel<T>(sql, dic.ToParameters(this));

        /// <summary>
        /// 查询到单个模型,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">生成where子句的字典</param>
        /// <returns></returns>
        public virtual T SelectModelFast<T>(string tableName, Dictionary<string, object> whereDic = null) where T : class, new() => SelectModelsFast<T>(tableName, whereDic).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">生成where子句的字典</param>
        /// <returns></returns>
        public virtual List<T> SelectModelsFast<T>(string tableName, Dictionary<string, object> whereDic = null) where T : class, new()
        {
            var (filter, paras2) = SqlUtil.GenerateFilterSql(this, whereDic, "p_", null);
            var sql = $"select * from {tableName} where 1=1 {filter}";
            return SelectModels<T>(sql, paras2);
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual T SelectModel<T>(string sql, params IDataParameter[] filterParameters) where T : class, new() => SelectModels<T>(sql, filterParameters).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql) where T : class, new() => SelectModels<T>(sql, new IDataParameter[] { });

        /// <summary>
        /// 查询到模型集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql, Dictionary<string, object> dic) where T : class, new() => SelectModels<T>(sql, dic.ToParameters(this));

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual List<T> SelectModels<T>(string sql, params IDataParameter[] filterParameters) where T : class, new() => SelectDataTable(sql, filterParameters).ToModels<T>();

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql) => SelectDictionarys(sql, new IDataParameter[] { }).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql, Dictionary<string, object> dic) => SelectDictionarys(sql, dic.ToParameters(this)).FirstOrDefault();

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual Dictionary<string, object> SelectDictionary(string sql, params IDataParameter[] filterParameters) => SelectDictionarys(sql, filterParameters).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql) => SelectDictionarys(sql, new IDataParameter[] { });

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql, Dictionary<string, object> dic) => SelectDictionarys(sql, dic.ToParameters(this));

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <returns></returns>
        public virtual List<Dictionary<string, object>> SelectDictionarys(string sql, params IDataParameter[] filterParameters) => SelectDataTable(sql, filterParameters).ToDictionaryList();
        #endregion

        #region 事务操作
        private int KeepTransactionCount = 0;
        /// <summary>
        /// 开启一次事务(不支持嵌套事务,如果当前已经在事务中并且隔离级别大于新指定的隔离级别,那么就忽略本次的开启事务操作)
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别(默认为:ReadCommitted)</param>
        private void BeginTransactionOnce(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (IsTran && Tran.IsolationLevel < isolationLevel) throw new Exception($"已经在事务中,并且当前的事务隔离级别({Tran.IsolationLevel.ToString()})小于新指定的隔离级别({isolationLevel})!");
            KeepTransactionCount++;
            if (!IsTran)
            {
                OpenLongConnectOnce();
                Tran = Conn.BeginTransaction(isolationLevel);
            }
        }

        /// <summary>
        /// 提交一次事务
        /// </summary>
        private void CommitTransactionOnce()
        {
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                Tran.Commit();
                Tran = null;
                CloseLongConnectOnce();
            }
        }

        /// <summary>
        /// 回滚一次事务
        /// </summary>
        private void RollbackTransactionOnce()
        {
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                Tran.Rollback();
                Tran = null;
                CloseLongConnectOnce();
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="action">要运行的代码</param>
        public void RunInTransaction(Action action, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                BeginTransactionOnce(isolationLevel);
                action();
                CommitTransactionOnce();
            }
            catch
            {
                RollbackTransactionOnce();
                throw;
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="func">要运行的代码</param>
        public T RunInTransaction<T>(Func<T> func, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                BeginTransactionOnce(isolationLevel);
                var res = func();
                CommitTransactionOnce();
                return res;
            }
            catch
            {
                RollbackTransactionOnce();
                throw;
            }
        }
        #endregion

        #region 生成分页查询语句: GetSqlForPageSize
        /// <summary>
        /// 生成分页查询语句
        /// </summary>
        /// <param name="selectSql">select子句,如:select id,name from person where age>10</param>
        /// <param name="strOrder">排序子句,必须指定,如:order by id</param>
        /// <param name="PageSize">分页大小,如:10</param>
        /// <param name="PageIndex">当前页码,如:1</param>
        /// <returns>返回示例(mysql):select id,name from person where age>10 order by id limit 0,10</returns>
        public abstract string GetSqlForPageSize(string selectSql, string strOrder, int PageSize, int PageIndex);
        #endregion

        #region Judge系列: 判断表/视图/列/存储过程/触发器是否存在       

        /// <summary>
        /// 判断指定表或视图中是否有某一列
        /// </summary>
        /// <param name="tableOrViewName">表或视图名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInTableOrView(string tableOrViewName, string columnName)
        {
            var tuple = AnalysisIdentifier(tableOrViewName);
            string sql = $@"select count(1)
from INFORMATION_SCHEMA.COLUMNS
where
    TABLE_NAME='{DealSqlInject(tuple.identityName)}'
    and
    COLUMN_NAME='{DealSqlInject(columnName)}'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $"\r\n and t.TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            }
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断指定表中是否有某一列
        /// </summary>
        /// <param name="tableName">表名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInTable(string tableName, string columnName)
        {
            var tuple = AnalysisIdentifier(tableName);
            string sql = $@"select count(1)
from INFORMATION_SCHEMA.COLUMNS c
    left join INFORMATION_SCHEMA.tables t
    on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME
where
    c.COLUMN_NAME='{DealSqlInject(columnName)}'
    and
    c.TABLE_NAME='{DealSqlInject(tuple.identityName)}'
    and t.TABLE_TYPE='BASE TABLE'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $"\r\n and t.TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            }
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断指定视图中是否有某一列
        /// </summary>
        /// <param name="viewName">视图名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <param name="columnName">列名</param>
        /// <returns>返回列是否存在</returns>
        public virtual bool JudgeColumnExistInView(string viewName, string columnName)
        {
            var tuple = AnalysisIdentifier(viewName);
            string sql = $@"select count(1)
from INFORMATION_SCHEMA.COLUMNS c
    left join INFORMATION_SCHEMA.tables t
    on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME
where
    c.COLUMN_NAME='{DealSqlInject(columnName)}'
    and
    c.TABLE_NAME='{DealSqlInject(tuple.identityName)}'
    and t.TABLE_TYPE='VIEW'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $"\r\n and t.TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            }
            int r = int.Parse(SelectScalar<string>(sql));
            return r > 0;
        }

        /// <summary>
        /// 判断表或视图是否存在
        /// </summary>
        /// <param name="tableOrViewName">表或视图名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <returns>返回表或视图是否存在</returns>
        public virtual bool JudgeTableOrViewExist(string tableOrViewName)
        {
            var tuple = AnalysisIdentifier(tableOrViewName);
            string sql = $"select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{DealSqlInject(tuple.identityName)}'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">表名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <returns>返回表是否存在</returns>
        public virtual bool JudgeTableExist(string tableName)
        {
            var tuple = AnalysisIdentifier(tableName);
            string sql = $"select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{DealSqlInject(tuple.identityName)}' and TABLE_TYPE='BASE TABLE'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断视图是否存在
        /// </summary>
        /// <param name="viewName">视图名，如: [dbo].[table1]、table1、`table1`、`testdb`.`table1`</param>
        /// <returns>返回视图是否存在</returns>
        public virtual bool JudgeViewExist(string viewName)
        {
            var tuple = AnalysisIdentifier(viewName);
            string sql = $"select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{DealSqlInject(tuple.identityName)}' and TABLE_TYPE='VIEW'";
            if (tuple.schemaName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and TABLE_SCHEMA='{DealSqlInject(tuple.schemaName)}'";
            int r = SelectScalar<int>(sql);
            return r > 0;
        }

        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public abstract bool JudgeProcedureExist(string procName);

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <returns></returns>
        public abstract bool JudgeTriggerExist(string triggerName);
        #endregion

        #region CreateNewDB
        /// <summary>
        /// 根据当前的数据库类型和连接字符串创建一个新的数据库操作对象
        /// </summary>
        /// <returns></returns>
        public DBAccess CreateNewDB(Action<DBSettings> reset = null)
        {
            var settings = Settings.Clone() as DBSettings;
            reset?.Invoke(settings);
            return DBFactory.CreateDB(DBType, DBConn, settings);
        }
        #endregion

        #region Id和流水号生成控制器
        /// <summary>
        /// 初始化基础数据库缓存的Id和流水号生成环境
        /// </summary>
        /// <returns></returns>
        protected abstract bool InitDBCacheGenerator();

        private static ConcurrentDictionary<string, bool> _initGenerators = new ConcurrentDictionary<string, bool>();
        /// <summary>
        /// 确保已经调用过<seealso cref="InitDBCacheGenerator"/>方法
        /// </summary>
        protected void EnsureInitGenerator()
        {
            if (!_initGenerators.TryGetValue(DBConn, out bool flag))
            {
                lock (typeof(DBAccess))
                {
                    if (!_initGenerators.TryGetValue(DBConn, out bool flag2))
                    {
                        var b = InitDBCacheGenerator();
                        if (b)
                        {
                            _initGenerators.TryAdd(DBConn, true);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        protected abstract long InternalNewId(string tableName, string colName);
        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <returns></returns>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行Id生成</remarks>
        public virtual long NewId(string tableName, string colName) => CreateNewDB().InternalNewId(tableName, colName);

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        protected abstract List<long> InternalNewIds(string tableName, string colName, int count);
        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <returns></returns>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行Id生成</remarks>
        public virtual List<long> NewIds(string tableName, string colName, int count) => CreateNewDB().InternalNewIds(tableName, colName, count);

        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <returns></returns>
        protected virtual void InternalResetId(string tableName, string colName)
        {
            EnsureInitGenerator();
            tableName = QuoteIdentifierWithDefaultSchema(tableName, DefaultSchema);
            colName = QuoteIdentifierIfNot(colName);
            Delete(Settings.DBCacheGeneratorIdTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
        }

        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <returns></returns>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行Id重置</remarks>
        public virtual void ResetId(string tableName, string colName) => CreateNewDB().InternalResetId(tableName, colName);

        /// <summary>
        /// 解析格式准备流水号生成
        /// </summary>
        /// <param name="format"></param>
        /// <param name="now"></param>
        /// <param name="machineIdString"></param>
        /// <returns></returns>
        protected (string likestr, DateTime snoNow, int startindex, string statictext, string machineIdString) PrePareForSNO(SerialFormat format, DateTime now, string machineIdString)
        {
            if (machineIdString.IsNullOrEmptyOrWhiteSpace()) machineIdString = DotNetCommon.Machine.MachineIdString;
            var (likestr, snoNow, startindex) = SerialFormat.Parse(format, now, machineIdString);
            var statictext = "";
            var chunks = format.Chunks;
            for (int i = 0, len = chunks.Count; i < len; i++)
            {
                var _chunk = chunks[i];
                if (_chunk.Type == SerialFormatChunkType.StaticText)
                {
                    statictext += _chunk.FormatString;
                }
                else if (_chunk.Type == SerialFormatChunkType.MachineText)
                {
                    statictext += machineIdString.PadLeft(4, '0');
                }
            }
            return (likestr, snoNow, startindex, statictext, machineIdString);
        }

        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        protected abstract string InternalNewSNO(string tableName, string colName, SerialFormat format);
        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <returns></returns>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行流水号生成</remarks>
        public virtual string NewSNO(string tableName, string colName, SerialFormat format) => CreateNewDB().InternalNewSNO(tableName, colName, format);

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        protected abstract List<string> InternalNewSNOs(string tableName, string colName, SerialFormat format, int count);
        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <returns></returns>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行流水号生成</remarks>
        public virtual List<string> NewSNOs(string tableName, string colName, SerialFormat format, int count) => CreateNewDB().InternalNewSNOs(tableName, colName, format, count);

        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat">格式</param>
        protected virtual void InternalResetSNO(string tableName, string colName, SerialFormat serialFormat = null)
        {
            EnsureInitGenerator();
            tableName = QuoteIdentifierWithDefaultSchema(tableName, DefaultSchema);
            colName = QuoteIdentifierIfNot(colName);
            if (serialFormat != null)
            {
                var text = PrePareForSNO(serialFormat, DateTime.Now, DotNetCommon.Machine.MachineIdString).statictext;
                Delete(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname and statictext={ParaPrefix}statictext", CreatePara("tablename", tableName), CreatePara("colname", colName), CreatePara("statictext", text));
            }
            else
            {
                Delete(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", CreatePara("tablename", tableName), CreatePara("colname", colName));
            }
        }
        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat">格式</param>
        /// <remarks>注意：为了避免受事务的影响，该方法将调用<seealso cref="CreateNewDB(Action{DBSettings})"/>生成新的数据库连接，然后再执行流水号重置</remarks>
        public virtual void ResetSNO(string tableName, string colName, SerialFormat serialFormat = null) => CreateNewDB().InternalResetSNO(tableName, colName);
        #endregion

        #region 基于数据库的分布式锁 RunInLock/TryGetLock/TryReleaseLock
        private static ConcurrentDictionary<string, bool> _lockcaches = new ConcurrentDictionary<string, bool>();
        private static ConcurrentDictionary<string, CancellationTokenSource> _locktokens = new ConcurrentDictionary<string, CancellationTokenSource>();

        private void EnsureInitLock()
        {
            if (!_lockcaches.ContainsKey(DBConn))
            {
                lock (typeof(DBAccess))
                {
                    if (!_lockcaches.ContainsKey(DBConn))
                    {
                        if (!JudgeTableExist(Settings.DBLockTableName))
                        {
                            #region 新建表 Settings.DBLockTableName
                            ExecuteSql($@"
create table {Settings.DBLockTableName}(
	lock_str varchar(200) unique not null,
	lock_time {Expression_DateTime.DefaultDateTimeType} default({Expression_DateTime.Current}),
    lock_createtime {Expression_DateTime.DefaultDateTimeType} default({Expression_DateTime.Current}),
	lock_user varchar(200) not null
)");
                            #endregion
                        }
                        _lockcaches.TryAdd(DBConn, true);
                    }
                }
            }
        }

        private Result TryGetLock(string lock_str, string lock_user, long timeoutSecond = 60 * 5)
        {
            return Result.Wrap(() =>
            {
                var starttime = DateTime.Now;
                var lockkey = GetLockKey(lock_str, lock_user);
                //单机做一次拦截
                while (true)
                {
                    if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
                    {
                        //指定的锁已经在使用中       
                        var endtime = DateTime.Now;
                        var span = endtime - starttime;
                        if (span.TotalSeconds > timeoutSecond)
                        {
                            throw new Exception($"获取锁[{lock_str}]超时!");
                        }
                        else
                        {
                            continue;
                        }
                    }
                    break;
                }
                //数据库层拦截
                using (OpenLongConnectOnce())
                {
                    EnsureInitLock();
                    while (true)
                    {
                        try
                        {
                            ExecuteSql($"insert into {Settings.DBLockTableName}(lock_str,lock_user) values('{lock_str}','{lock_user}')");
                            StartLockMonitor(lock_str, lock_user);
                            break;
                        }
                        catch (Exception ex)
                        {
                            var endtime = DateTime.Now;
                            var span = endtime - starttime;
                            if (span.TotalSeconds > timeoutSecond)
                            {
                                throw new Exception($"获取锁[{lock_str}]超时:{ex?.Message}]", ex);
                            }
                            Thread.Sleep(1000);
                            //删除30秒之前的
                            ExecuteSql($"delete from {Settings.DBLockTableName} where lock_time <{Expression_DateTime.GetCurrentAddSecond(-30)}");
                        }
                    }
                }
            });
        }

        private void StartLockMonitor(string lock_str, string lock_user)
        {
            var cancellationTokenSource = new CancellationTokenSource();
            _locktokens.TryAdd(GetLockKey(lock_str, lock_user), cancellationTokenSource);
            Task.Run(() =>
            {
                DBAccess db2 = CreateNewDB();
                while (true)
                {
                    if (cancellationTokenSource.IsCancellationRequested) break;
                    //休息10秒
                    Thread.Sleep(1000 * 10);
                    //将当前的锁刷新时长
                    int count = db2.ExecuteSql($"update {Settings.DBLockTableName} set lock_time= {Expression_DateTime.Current} where lock_str='{lock_str}' and lock_user='{lock_user}'");
                    if (count != 1) break;
                }
            });
        }

        /// <summary>
        /// 在分布式锁内执行代码逻辑
        /// </summary>
        /// <param name="lock_str">锁定的资源名称</param>
        /// <param name="action">要执行的代码逻辑</param>
        /// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        public void RunInLock(string lock_str, Action action, long getLockTimeoutSecond = 60 * 5)
        {
            var lock_user = $"{DateTime.Now.ToCommonStampString()} {Guid.NewGuid().ToString().Replace("-", "").ToLower()}";
            var res = TryGetLock(lock_str, lock_user, getLockTimeoutSecond);
            if (res.Success)
            {
                try
                {
                    action();
                }
                finally
                {
                    TryReleaseLock(lock_str, lock_user);
                }
            }
            else
            {
                throw new Exception(res.Message);
            }
        }

        /// <summary>
        /// 在分布式锁内执行代码逻辑,如果获取锁超时则抛出异常
        /// </summary>
        /// <param name="lock_str">锁定的资源名称</param>
        /// <param name="func">要执行的代码逻辑</param>
        /// <param name="getLockTimeoutSecond">获取锁的超时时间(秒),默认5分钟</param>
        public T RunInLock<T>(string lock_str, Func<T> func, long getLockTimeoutSecond = 60 * 5)
        {
            var lock_user = $"{DateTime.Now.ToCommonStampString()} {Guid.NewGuid().ToString().Replace("-", "").ToLower()}";
            var res = TryGetLock(lock_str, lock_user, getLockTimeoutSecond);
            if (res.Success)
            {
                try
                {
                    return func();
                }
                finally
                {
                    TryReleaseLock(lock_str, lock_user);
                }
            }
            else
            {
                throw new TimeoutException(res.Message);
            }
        }

        private void TryReleaseLock(string lock_str, string lock_user)
        {
            var lockkey = GetLockKey(lock_str, lock_user);
            EnsureInitLock();
            if (_locktokens.TryGetValue(lockkey, out CancellationTokenSource token))
            {
                token.Cancel();
                _locktokens.TryRemove(lockkey, out CancellationTokenSource token2);
            }
            ExecuteSql($"delete from {Settings.DBLockTableName} where lock_str='{lock_str}' and lock_user='{lock_user}'");
        }

        private string GetLockKey(string lock_str, string lock_user)
        {
            return $"{lock_str}_{lock_user}";
        }
        #endregion

        #region 数据库管理对象 Manage
        /// <summary>
        /// 数据库管理对象
        /// </summary>
        public abstract DBManage Manage { get; }
        #endregion

        #region 运算符
        /// <summary>
        /// 数据库日期时间运算符
        /// </summary>
        public virtual DateTimeExpression Expression_DateTime { get; }

        /// <summary>
        /// 数据库字符串运算符
        /// </summary>
        public virtual StringExpression Expression_String { get; }
        #endregion

        #region 获取刚插入的自增id的Sql语句
        /// <summary>
        /// 获取刚插入的自增id的Sql语句
        /// </summary>
        /// <param name="sequenceName">序列名称</param>
        /// <returns></returns>
        public abstract string GetInsertedId(string sequenceName = null);
        #endregion
    }
}